import bean.Repertory;
import bean.User;
import bean.attendance;
import bean.wareHouse;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.c3p0Utils;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * @Author guoxiuwei
 * @Date 2020/8/24 10:55
 * @Title
 */
public class test03 {
    private static QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
    public static void main(String[] args) {

        QueryRunner qr=new QueryRunner(c3p0Utils.getDataSource());
        String sql1="SELECT SUM(merchSum) ADDmerchSum FROM Repertory WHERE warehouseId=?";
        List<wareHouse> list=null;
        try {
            list=qr.query("select * from WareHouse where isalive=?",new BeanListHandler<wareHouse>(wareHouse.class),1);
            for (int i=0;i<list.size();i++){
                wareHouse w=qr.query(sql1,new BeanHandler<wareHouse>(wareHouse.class),list.get(i).getWarehouseId());
                list.get(i).setSurplusCapacity(list.get(i).getWarehouseCap()-w.getADDmerchSum());
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(list);
    }
}
